#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
CREATE database IF NOT EXISTS edu_dm;

CREATE TABLE edu_dm.dm_signin_result(
    class_date             STRING    COMMENT '上课日期',
    class_id               INT       COMMENT '班级id',
    morning_signin_count   BIGINT    COMMENT '早上正常出勤人数',
    morning_signin_rate    STRING    COMMENT '早上正常出勤率',
    afternoon_signin_count BIGINT    COMMENT '下午正常出勤人数',
    afternoon_signin_rate  STRING    COMMENT '下午正常出勤率',
    evening_signin_count   BIGINT    COMMENT '晚上正常出勤人数',
    evening_signin_rate    STRING    COMMENT '晚上正常出勤率'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '/t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'snappy');


CREATE TABLE edu_dm.dm_leave_result(
    class_date             STRING    COMMENT '上课日期',
    class_id               INT       COMMENT '班级id',
    morning_leave_count    BIGINT    COMMENT '上午请假人数',
    morning_leave_rate     STRING    COMMENT '上午请假率',
    afternoon_leave_count  BIGINT    COMMENT '下午请假人数',
    afternoon_leave_rate   STRING    COMMENT '下午请假率',
    evening_leave_count    BIGINT    COMMENT '晚上请假人数',
    evening_leave_rate     STRING    COMMENT '晚上请假率'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '/t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'snappy');



CREATE TABLE edu_dm.dm_late_result(
    class_date            STRING    COMMENT '上课日期',
    class_id              INT       COMMENT '班级id',
    morning_late_count    BIGINT    COMMENT '上午迟到人数',
    morning_late_rate     STRING    COMMENT '上午迟到率',
    afternoon_late_count  BIGINT    COMMENT '下午迟到人数',
    afternoon_late_rate   STRING    COMMENT '下午迟到率',
    evening_late_count    BIGINT    COMMENT '晚上迟到人数',
    evening_late_rate     STRING    COMMENT '晚上迟到率'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '/t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'snappy');



CREATE TABLE edu_dm.dm_absent_result(
    class_date            STRING    COMMENT '上课日期',
    class_id              INT       COMMENT '班级id',
    morning_absent_count    BIGINT    COMMENT '上午旷课人数',
    morning_absent_rate     STRING    COMMENT '上午旷课率',
    afternoon_absent_count  BIGINT    COMMENT '下午旷课人数',
    afternoon_absent_rate   STRING    COMMENT '下午旷课率',
    evening_absent_count    BIGINT    COMMENT '晚上旷课人数',
    evening_absent_rate     STRING    COMMENT '晚上旷课率'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '/t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'snappy');